package com.x.erp.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.x.erp.model.Model;
import com.x.framework.dao.BaseDao;
@Repository
public class ModelDao extends BaseDao {

	private final static String selectModelListSql = "SELECT T1.*,T2.SERIES_NAME,T3.BRAND_NAME,zs FROM x_base.BASE_MODEL T1 join  x_base.BASE_SERIES T2 on T1.SERIES_ID = T2.SERIES_ID join x_base.BASE_BRAND T3 on T2.BRAND_ID = T3.BRAND_ID " +
            "left join (select A.MODEL_ID, count(1) as zs from x_base.third_model_check as A where A.MODEL_PASS=0) as B on t1.MODEL_ID = B.MODEL_ID " +
            "where true ";
	public List<Model> selectModelList(Model model) throws Exception{
		StringBuilder sb = new StringBuilder(selectModelListSql);
		List<Object> params = new ArrayList<>();
		selectSearch(sb, params, model);
		sb.append("ORDER BY T1.MODEL_YEAR DESC, T1.MODEL_NAME ASC");
        if(model.getTruePage() != null){
            this.getLimitSql(sb, model);
        }
		return this.queryForList(sb.toString(), Model.class, params);
	}

	private final static String selectModelAndConfListSql = "SELECT T1.*,T2.SERIES_ID,T2.SERIES_NAME,T3.BRAND_ID,T3.BRAND_NAME,T4.* FROM x_base.BASE_MODEL T1, x_base.BASE_SERIES T2, x_base.BASE_BRAND T3, x_base.BASE_MODEL_CONF T4 WHERE T1.SERIES_ID = T2.SERIES_ID AND T2.BRAND_ID = T3.BRAND_ID AND T1.MODEL_ID = T4.MODEL_ID ";
	public List<Model> selectModelAndConfList(Model model) throws Exception{
		StringBuilder sql = new StringBuilder(selectModelAndConfListSql);
		List<Object> params = new ArrayList<>();
		selectSearch(sql, params, model);
		return this.queryForList(sql.toString(), Model.class, params);
	}

	private void selectSearch(StringBuilder sql, List<Object> params, Model model){
		if(model.getModelId() != null){
			sql.append("AND T1.MODEL_ID = ? ");
			params.add(model.getModelId());
		}
		if(this.isString(model.getModelName())){
			sql.append("AND T1.MODEL_NAME = ? ");
			params.add(model.getModelName());
		}
		if(model.getSeriesId() != null){
			sql.append("AND T2.SERIES_ID = ? ");
			params.add(model.getSeriesId());
		}
		if(this.isString(model.getSeriesName())){
			sql.append("AND T2.SERIES_NAME = ? ");
			params.add(model.getSeriesName());
		}
		if(this.isString(model.getBrandName())){
			sql.append("AND T3.BRAND_NAME = ? ");
			params.add(model.getBrandName());
		}
	}

	private final static String selectModelMappingSql = "SELECT " +
			"bm.*,bs.BRAND_ID " + 
			"FROM " + 
			"x_base.base_model bm, " +
			"x_base.base_series bs, " +
			"ERP_model_mapping emm " + 
			"WHERE " + 
			"bm.model_id = emm.model_id " + 
			"AND bm.SERIES_ID = bs.SERIES_ID " + 
			"AND emm.MARKET_MODEL_ID = ? " + 
			"AND emm.MARKET_ID = ? ";
	/**
	 * 查询车型关系
	 * @param model
	 * @param marketId
	 * @return
	 * @throws Exception 
	 */
	public Model selectModelMapping(Model model, String marketId) throws Exception {
		StringBuilder sql = new StringBuilder(selectModelMappingSql);
		List<Object> params = new ArrayList<Object>();
		params.add(model.getModelId());
		params.add(marketId);
		List<Model> models = this.queryForList(sql.toString(), Model.class, params);
		if(models != null && models.size() > 0) {
			return models.get(0);
		}
		return null;
	}

    /**
     * 添加
     */
    private final static String insertModelListSql = "INSERT INTO x_base.BASE_MODEL(MODEL_ID,MODEL_NAME,MODEL_YEAR" +
            "MODEL_GUIDE_PRICE,MODEL_DISPLACEMENT,MODEL_INLET_MODEL,MODEL_GEARBOX,MODEL_GEARBOX_MODALITY,MODEL_ENVIRON," +
            "MODEL_MANUFACTURER,MODEL_BODY,MODEL_BODYWORK,MODEL_LENGTH,MODEL_WIDTH,MODEL_HEIGHT,MODEL_WHEELBASE,MODEL_ENGINE_MODEL," +
            "MODEL_CYLINDER_ARRAY,MODEL_CYLINDER_NUM,MODEL_GEAR_NUM,MODEL_FUEL_ROZ,MODEL_SEAT,MODEL_MIN_YEAR,MODEL_MAX_YEAR,MODEL_DISCHARGE,SERIES_ID,MODEL_SALE_NAME) VALUES ";

    public void insertModelList(Model model) throws Exception{
        StringBuilder sql = new StringBuilder(insertModelListSql);
        List<Object> params = new ArrayList<>();
        if(model != null){
            sql.append("(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)") ;
            params.add(model.getModelId());
            params.add(model.getModelName());
            params.add(model.getModelYear());
            params.add(model.getModelGuidePrice());
            params.add(model.getModelDisplacement());
            params.add(model.getModelInletModel());
            params.add(model.getModelGearbox());
            params.add(model.getModelGearboxModality());
            params.add(model.getModelEnviron());
            params.add(model.getModelManufacturer());
            params.add(model.getModelBody());
            params.add(model.getModelBodywork());
            params.add(model.getModelLength());
            params.add(model.getModelWidth());
            params.add(model.getModelWheelbase());
            params.add(model.getModelEngineModel());
            params.add(model.getModelCylinderArray());
            params.add(model.getModelCylinderNum());
            params.add(model.getModelGearNum());
            params.add(model.getModelFuelRoz());
            params.add(model.getModelSeat());
            params.add(model.getModelMinYear());
            params.add(model.getModelMaxYear());
            params.add(model.getModelDisplacement());
            params.add(model.getModelInletModel());
            params.add(model.getSeriesId());
            params.add(model.getModelSaleName());
            this.execute(sql.toString(), params);
        }
    }

    /**
     * 修改
     */
    public void updateLibraryModelList(Model model) throws Exception{
        if(model != null){
            StringBuilder sql = new StringBuilder();
            List<Object> params = new ArrayList<>();
            sql.append("UPDATE x_base.BASE_MODEL set MODEL_NAME= ?,MODEL_YEAR= ?," +
                    " MODEL_GUIDE_PRICE= ?,MODEL_DISPLACEMENT= ?,MODEL_INLET_MODEL= ?,MODEL_GEARBOX= ?,MODEL_GEARBOX_MODALITY= ?,MODEL_ENVIRON= ?," +
                    " MODEL_MANUFACTURER= ?,MODEL_BODY= ?,MODEL_BODYWORK= ?,MODEL_LENGTH= ?,MODEL_WIDTH= ?,MODEL_HEIGHT= ?,MODEL_WHEELBASE= ?,MODEL_ENGINE_MODEL= ?," +
                    " MODEL_CYLINDER_ARRAY= ?,MODEL_CYLINDER_NUM= ?,MODEL_GEAR_NUM= ?,MODEL_FUEL_ROZ= ?,MODEL_SEAT= ?,MODEL_MIN_YEAR= ?,MODEL_MAX_YEAR= ?,MODEL_DISCHARGE= ?,SERIES_ID= ?,MODEL_SALE_NAME=? where MODEL_ID = ? ") ;
            params.add(model.getModelName());
            params.add(model.getModelYear());
            params.add(model.getModelGuidePrice());
            params.add(model.getModelDisplacement());
            params.add(model.getModelInletModel());
            params.add(model.getModelGearbox());
            params.add(model.getModelGearboxModality());
            params.add(model.getModelEnviron());
            params.add(model.getModelManufacturer());
            params.add(model.getModelBody());
            params.add(model.getModelBodywork());
            params.add(model.getModelLength());
            params.add(model.getModelWidth());
            params.add(model.getModelHeight());
            params.add(model.getModelWheelbase());
            params.add(model.getModelEngineModel());
            params.add(model.getModelCylinderArray());
            params.add(model.getModelCylinderNum());
            params.add(model.getModelGearNum());
            params.add(model.getModelFuelRoz());
            params.add(model.getModelSeat());
            params.add(model.getModelMinYear());
            params.add(model.getModelMaxYear());
            params.add(model.getModelEnviron());
            params.add(model.getSeriesId());
            params.add(model.getModelSaleName());
            params.add(model.getModelId());
            this.execute(sql.toString(), params);
        }
    }

    /**
     * 按照series id 查询
     */
    public Model getSimpleModel(Map<String,Object> model) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT T1.*,T2.SERIES_NAME,T3.BRAND_NAME,zs FROM x_base.BASE_MODEL T1 join  x_base.BASE_SERIES T2 on T1.SERIES_ID = T2.SERIES_ID join x_base.BASE_BRAND T3 on T2.BRAND_ID = T3.BRAND_ID " +
                " left join (select A.MODEL_ID, count(1) as zs from x_base.third_model_check as A where A.MODEL_PASS=0) as B on t1.MODEL_ID = B.MODEL_ID " +
                " where true ") ;
        List<Object> params = new ArrayList<>();
        if(model.containsKey("modelId")){
            params.add(model.get("modelId"));
            sql.append(" and T1.MODEL_ID = ? ") ;
        }
        if(model.containsKey("modelName")){
            params.add(model.get("modelName"));
            sql.append(" and T1.MODEL_NAME = ? ") ;
        }
        return (Model) this.queryForObject(sql.toString(), Model.class, params);
    }

    /**
     * 分页Sql
     * @param sql
     * @param model
     */
    private void getLimitSql(StringBuilder sql, Model model) {
        if(model.getPageSize() != null && model.getPageSize() > 0) {
            sql.append(" LIMIT ").append(model.getPageIndex()-1).append(",").append(model.getPageSize());
        } else {
            sql.append(" LIMIT 5");
        }
    }
}
